package sparksql_fenxi.T1

import org.apache.spark.sql.SparkSession

object t1 {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder()
      .appName("test")
      .master("local[*]")
      .enableHiveSupport()
      .getOrCreate()


    spark.table("test.score")
      .createTempView("score")

    spark.table("test.course")
      .createTempView("course")

    spark.table("test.teacher")
      .createTempView("teacher")


    val r1 = spark.sql(
      """
        |select distinct
        |c.t_id,
        |c.t_name,
        |a.c_id,
        |b.c_name,
        |avg(a.s_score) over(partition by a.c_id) as avg_score
        |from score as a
        |join course as b
        |on b.c_id=a.c_id
        |join teacher as c
        |on c.t_id=b.t_id
        |""".stripMargin)

    r1.show



    spark.close()
  }

}
